﻿using QWManage.Model;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;

namespace QWManage.DAL
{
    public class CustomersGroupsDAL : SqlDAL
    {
        public CustomersGroupsDAL()
        {
            TableName = "QW_Customers_Groups";
        }
        public void UpdateInGroupStatus(DateTime lastUpdate)
        {
            string sql = $"update {TableName} set Status=0 where lastUpdate!=@lastUpdate";
            SqlConnection conn = new SqlConnection(SQLCON);
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(new SqlParameter("@lastUpdate", lastUpdate));
                cmd.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }
        public void Add(CustomerGroupInfo info)
        {
            if (GetCustomerGroupInfo(info.UserID, info.GroupID) == null)
            {
                string sql = $"insert into {TableName} " +
                    $"(UserID,GroupID,LastUpdate,Status,Type,JoinTime,JoinScene,InvitorUserID,GroupNickname,Name) " +
                    $"values" +
                    $"(@UserID,@GroupID,@LastUpdate,@Status,@Type,@JoinTime,@JoinScene,@InvitorUserID,@GroupNickname,@Name)";

                SqlConnection conn = new SqlConnection(SQLCON);
                try
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    cmd.Parameters.Add(new SqlParameter("@UserID", info.UserID));
                    cmd.Parameters.Add(new SqlParameter("@GroupID", info.GroupID));
                    cmd.Parameters.Add(new SqlParameter("@LastUpdate", info.LastUpdate));
                    cmd.Parameters.Add(new SqlParameter("@Status", info.Status));

                    cmd.Parameters.Add(new SqlParameter("@Type", SqlIntNull(info.Type)));
                    cmd.Parameters.Add(new SqlParameter("@JoinTime", SqlIntNull(info.JoinTime)));
                    cmd.Parameters.Add(new SqlParameter("@JoinScene", SqlIntNull(info.JoinScene)));
                    cmd.Parameters.Add(new SqlParameter("@InvitorUserID", SqlNull(info.InvitorUserID)));
                    cmd.Parameters.Add(new SqlParameter("@GroupNickname", SqlNull(info.GroupNickname)));
                    cmd.Parameters.Add(new SqlParameter("@Name", SqlNull(info.Name)));


                    cmd.ExecuteNonQuery();
                }
                finally
                {
                    conn.Close();
                }
            }
            else
            {
                string sql = $"update {TableName} set" +
                    $" Type=@Type," +
                    $" JoinTime=@JoinTime," +
                    $" JoinScene=@JoinScene," +
                    $" InvitorUserID=@InvitorUserID," +
                    $" GroupNickname=@GroupNickname," +
                    $" Name=@Name," +
                    $" LastUpdate=@LastUpdate," +
                    $" Status=1" +
                    $" where UserID=@UserID and GroupID=@GroupID";
                SqlConnection conn = new SqlConnection(SQLCON);
                try
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    cmd.Parameters.Add(new SqlParameter("@LastUpdate", info.LastUpdate));
                    cmd.Parameters.Add(new SqlParameter("@UserID", info.UserID));
                    cmd.Parameters.Add(new SqlParameter("@GroupID", info.GroupID));

                    cmd.Parameters.Add(new SqlParameter("@Type", SqlIntNull(info.Type)));
                    cmd.Parameters.Add(new SqlParameter("@JoinTime", SqlIntNull(info.JoinTime)));
                    cmd.Parameters.Add(new SqlParameter("@JoinScene", SqlIntNull(info.JoinScene)));
                    cmd.Parameters.Add(new SqlParameter("@InvitorUserID", SqlNull(info.InvitorUserID)));
                    cmd.Parameters.Add(new SqlParameter("@GroupNickname", SqlNull(info.GroupNickname)));
                    cmd.Parameters.Add(new SqlParameter("@Name", SqlNull(info.Name)));

                    cmd.ExecuteNonQuery();
                }
                finally
                {
                    conn.Close();
                }

            }
        }
        public HashSet<string> GetUserIDHashSet()
        {
            HashSet<string> set = new HashSet<string>();
            SqlConnection conn = new SqlConnection(SQLCON);
            try
            {
                string sql = "select UserID from " + TableName;
                SqlCommand cmd = new SqlCommand(sql, conn);
                conn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        string avatar = reader.GetString(0);
                        if (string.IsNullOrEmpty(avatar)) continue;
                        set.Add(avatar);
                    }
                }
                return set;
            }
            finally
            {
                conn.Close();
            }
        }
        public HashSet<string> GetMemberInGroupHashSet(int status = 1)
        {
            HashSet<string> hashset = new HashSet<string>();
            SqlConnection conn = new SqlConnection(SQLCON);
            try
            {
                string sql = $"select distinct UserID from {TableName} where Status=" + status;
                SqlCommand cmd = new SqlCommand(sql, conn);
                conn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        hashset.Add(reader.GetString(0));
                    }
                }
                return hashset;
            }
            finally
            {
                conn.Close();
            }
        }
        public List<CustomerGroupInfo> GetCustomerGroupInfos()
        {
            List<CustomerGroupInfo> list = new List<CustomerGroupInfo>();
            SqlConnection conn = new SqlConnection(SQLCON);
            try
            {
                string sql = "select * from " + TableName;
                SqlCommand cmd = new SqlCommand(sql, conn);
                conn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        CustomerGroupInfo obj = GetCustomerGroupInfo(reader);
                        list.Add(obj);
                    }
                }
                return list;
            }
            finally
            {
                conn.Close();
            }
        }
        public List<CustomerGroupInfo> GetCustomerGroupInfosByGroupID(string groupID)
        {
            List<CustomerGroupInfo> list = new List<CustomerGroupInfo>();
            SqlConnection conn = new SqlConnection(SQLCON);
            try
            {
                string sql = $"select * from {TableName} where GroupID=@GroupID order by Status";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(new SqlParameter("@GroupID", groupID));
                conn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        CustomerGroupInfo obj = GetCustomerGroupInfo(reader);
                        list.Add(obj);
                    }
                }
                return list;
            }
            finally
            {
                conn.Close();
            }
        }
        public List<CustomerGroupInfo> GetCustomerGroupInfosByUserID(string userid)
        {
            List<CustomerGroupInfo> list = new List<CustomerGroupInfo>();
            SqlConnection conn = new SqlConnection(SQLCON);
            try
            {
                string sql = $"select * from {TableName} where UserID=@UserID";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(new SqlParameter("@UserID", userid));
                conn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        CustomerGroupInfo obj = GetCustomerGroupInfo(reader);
                        list.Add(obj);
                    }
                }
                return list;
            }
            finally
            {
                conn.Close();
            }
        }
        public CustomerGroupInfo GetCustomerGroupInfo(string userid, string groupid)
        {
            CustomerGroupInfo obj = null;
            SqlConnection conn = new SqlConnection(SQLCON);
            try
            {
                string sql = $"select * from {TableName} where UserID=@UserID and GroupID=@GroupID";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(new SqlParameter("@UserID", userid));
                cmd.Parameters.Add(new SqlParameter("@GroupID", groupid));
                conn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        obj = GetCustomerGroupInfo(reader);
                    }
                }
                return obj;
            }
            finally
            {
                conn.Close();
            }
        }
        private CustomerGroupInfo GetCustomerGroupInfo(SqlDataReader reader)
        {
            CustomerGroupInfo obj = new CustomerGroupInfo();
            obj.UserID = ReadString(reader, "UserID");
            obj.GroupID = ReadString(reader, "GroupID");
            obj.LastUpdate = (DateTime)reader["LastUpdate"];
            obj.Status = ReadBool(reader, "Status");
            //add 2023-12-14

            obj.Type = ReadIntNull(reader, "Type");
            obj.JoinTime = ReadIntNull(reader, "JoinTime");
            obj.JoinScene = ReadIntNull(reader, "JoinScene");

            obj.InvitorUserID = ReadString(reader, "InvitorUserID");
            obj.GroupNickname = ReadString(reader, "GroupNickname");
            obj.Name = ReadString(reader, "Name");

            return obj;
        }
    }
}
